#!/usr/bin/env python2
#-*- coding: utf-8 -*-

import json
import logging
import MySQLdb
import MySQLdb.cursors

groups_tab = 'groups'
vols_tab = 'vols'
snaps_tab = 'snaps'
snap_vols_tab = 'snap_vols'

class _MySQL(object):
    def __init__(self, host, port, user, passwd, db, charset='utf8'):
        self.conn = MySQLdb.connect(
                host = host,
                port = port,
                user = user,
                passwd = passwd,
                charset = charset,
                cursorclass = MySQLdb.cursors.DictCursor)

        self.__db_init(db)

    def __db_init(self, db):
        cur = self.get_cursor()  
        cur.execute("SHOW DATABASES;")
        dbs = [row['Database'] for row in cur.fetchall()]
        if db in dbs:
            self.conn.select_db(db)
            return 

        # database
        cur.execute('CREATE DATABASE IF NOT EXISTS %s;'% db)
        self.conn.select_db(db)

        # tables
        sql = 'CREATE TABLE IF NOT EXISTS %s (\
                `id` int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,\
                `name` varchar(255) NOT NULL,\
                `ctime` datetime DEFAULT NULL,\
                `owner` varchar(255) DEFAULT NULL\
                );' % groups_tab
        cur.execute(sql)

        sql = 'CREATE TABLE IF NOT EXISTS %s (\
                `id` int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,\
                `name` varchar(255) NOT NULL,\
                `group_id` int(255) NOT NULL,\
                `ctime` datetime DEFAULT NULL,\
                `owner` varchar(255) DEFAULT NULL\
                );' % vols_tab
        cur.execute(sql)

        sql = 'CREATE TABLE IF NOT EXISTS %s (\
                `id` int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,\
                `name` varchar(255) NOT NULL,\
                `group_id` int(255) NOT NULL,\
                `ctime` datetime DEFAULT NULL,\
                `owner` varchar(255) DEFAULT NULL\
                );' % snaps_tab
        cur.execute(sql)

        sql = 'CREATE TABLE IF NOT EXISTS %s (\
                `id` int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,\
                `snap_id` int(255) NOT NULL,\
                `vol_id` int(255) NOT NULL,\
                `group_id` int(255) NOT NULL,\
                `ctime` datetime DEFAULT NULL,\
                `owner` varchar(255) DEFAULT NULL\
                );' % snap_vols_tab
        cur.execute(sql)

    def get_cursor(self):
        return self.conn.cursor()

    def query(self, sql):
        cursor = self.get_cursor()  
        try:
            cursor.execute(sql, None)
            result = cursor.fetchall()  
        except Exception, e:
            logging.error("mysql query error: %s", e)
            return None
        finally:
            cursor.close()
        return result

    def execute(self, sql, param=None):
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, param)
            self.conn.commit()
            affected_row = cursor.rowcount
        except Exception, e:
            logging.error("mysql execute error: %s", e)
            return 0
        finally:
            cursor.close()
        return affected_row

    def executemany(self, sql, params=None):
        cursor = self.get_cursor()
        try:
            cursor.executemany(sql, params)
            self.conn.commit()
            affected_rows = cursor.rowcount
        except Exception, e:
            logging.error("mysql executemany error: %s", e)
            return 0
        finally:
            cursor.close()
        return affected_rows

    def close(self):
        try:
            self.conn.close()
        except:
            pass

    def __del__(self):
        self.close()

def test():
    host = 'localhost'
    port = 3306
    user = 'root'
    passwd = 'mdsmds'
    db = 'group_db'

    mysql = _MySQL(host, port, user, passwd, db)
    def create_table():
        table = """
                CREATE TABLE IF NOT EXISTS `watchdog`(
                    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `name` varchar(100),
                    `price` int(11) NOT NULL DEFAULT 0
                ) ENGINE=InnoDB charset=utf8;
                """
        print mysql.execute(table)

    def insert_data():
        params = [('dog_%d' % i, i) for i in xrange(12)]
        sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"   
        print mysql.executemany(sql, params) 

    def query():
        sql = "show databases;"
        _dbs = mysql.query(sql)
        dbs = json.dumps([row['Database'] for row in _dbs])
        print dbs

    def delete_database():
        sql = "drop database if exists %s;" %db
        print mysql.execute(sql)
    

    #delete_database()
    query()

if __name__ == '__main__':
    test()



